# @Project: diablo
# @File: migrate_quality
# @Time: 2023/11/20 20:48
# @User: Jolly
# @Email: jolly@007vin.com

"""
初始化测试品质映射
"""

import pymysql
import time
import threading

from dbutils.pooled_db import PooledDB


lock = threading.Lock()


class QualityInit:

    def __init__(self):

        start_time = time.time()

        self.quality_dict = {}

        # 初始化数据库连接

        self.mysql_conn = self.get_mysql_connection()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def get_quality_data(self, merchant_id):
        """
        获取商品品质数据
        """

        quality_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select app_key, ori_quality, quality, quality_py from diablo_quality_replace where merchant_id='{merchant_id}' and status=1"""

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    quality_list.append({
                        "status": 1,
                        "merchant_id": "777",
                        "app_key": result_[0],
                        "ori_quality": result_[1],
                        "quality": result_[2],
                        "quality_py": result_[3],
                        "create_time": "2023-11-20 20:00:00",
                        "update_time": "2023-11-20 20:00:00",
                    })

        except Exception as e:
            print("获取品质信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return quality_list

    def get_quality_zhibao_data(self, merchant_id):
        """
        获取质保
        """

        zhibao_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select quality_name, quality_pinyin, quality_pinyin_initials, warranty, agreement_type,
             stocks_amount, stocks_total from diablo_quality_tags where status=1 and merchant_id='{merchant_id}' """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    zhibao_list.append({
                        "user_id": 0,
                        "merchant_id": "777",
                        "quality_name": result_[0],
                        "quality_pinyin": result_[1],
                        "quality_pinyin_initials": result_[2],
                        "warranty": result_[3],
                        "status": 1,
                        "agreement_type": result_[4],
                        "stocks_amount": result_[5],
                        "stocks_total": result_[6],
                    })

        except Exception as e:
            print("获取质保信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return zhibao_list

    def insert_data1(self, quality_list):
        """
        插入信息
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            for quality in quality_list:

                key = ['status', 'merchant_id', 'app_key', 'ori_quality', 'quality',
                       'quality_py', 'create_time', 'update_time']

                key_param = [quality.get('status'), quality.get('merchant_id'), quality.get('app_key'),
                             quality.get('ori_quality'), quality.get('quality'), quality.get('quality_py'),
                             quality.get('create_time'), quality.get('update_time')]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                try:
                    sql = '''insert into diablo_quality_replace ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                        ",".join(key), value[:-1], values)
                    # print(sql)
                    cursor.execute(sql)
                    mysql_conn.commit()
                except Exception as e:
                    pass

        except Exception as e:
            print("获取用户分公司信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return {}

    def insert_data2(self, zhibao_list):
        """
        插入信息
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            for zhibao in zhibao_list:

                # "user_id": 0,
                # "merchant_id": "e36cf07027ec40aca7be752d45dc84d6",
                # "quality_name": result_[0],
                # "quality_pinyin": result_[1],
                # "quality_pinyin_initials": result_[2],
                # "warranty": result_[3],
                # "status": 1,
                # "agreement_type": result_[4],
                # "stocks_amount": result_[5],
                # "stocks_total": result_[6],

                key = ['user_id', 'merchant_id', 'quality_name', 'quality_pinyin', 'quality_pinyin_initials',
                       'warranty', 'status', 'agreement_type', 'stocks_amount', 'stocks_total']

                key_param = [zhibao.get('user_id'), zhibao.get('merchant_id'), zhibao.get('quality_name'),
                             zhibao.get('quality_pinyin'), zhibao.get('quality_pinyin_initials'), zhibao.get('warranty'),
                             zhibao.get('status'), zhibao.get('agreement_type'), zhibao.get('stocks_amount'), zhibao.get('stocks_total') ]

                value = ""

                for i, v in enumerate(key):
                    value += '"{}",'.format(key_param[i])

                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                try:
                    sql = '''insert into diablo_quality_tags ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                        ",".join(key), value[:-1], values)
                    cursor.execute(sql)
                    mysql_conn.commit()
                except Exception as e:
                    pass

                # print(sql)

        except Exception as e:
            print("获取用户分公司信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return {}


if __name__ == "__main__":
    service = QualityInit()
    quality_list = service.get_quality_data("3")

    zhibao_list = service.get_quality_zhibao_data("3")

    service.insert_data1(quality_list)

    service.insert_data2(zhibao_list)
